package queries;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import connect.DBConnectionUtil;

public class Query3 extends JFrame{
	public Query3(){
		this.setBounds(300,240,500,400);
		this.setLayout(null);
		JLabel label0 = new JLabel("Enter patient id to get patient's test");
		label0.setBounds(200, 10, 500, 30);
		this.add(label0);
		JLabel label1 = new JLabel("Pid:");
		JTextField pid = new JTextField();
		label1.setBounds(100, 50, 100, 30);
		pid.setBounds(200, 50, 200, 30);
		this.add(label1);
		this.add(pid);
		
		
		JButton query = new JButton("Query");
		query.setBounds(200, 100, 100, 30);
		this.add(query);
		this.setTitle("Query3:Get patient's test");
		this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		
		JTable jtable = new JTable();
		JScrollPane pane = new JScrollPane(jtable);
		pane.setBounds(100, 150, 300, 100);
		this.add(pane);
		this.setVisible(true);
		query.addActionListener(new ActionListener(){

			@Override
			public void actionPerformed(ActionEvent arg0) {
				try {
					// 1.connect database
					Class.forName(DBConnectionUtil.DBDRIVER).newInstance();
					Connection con = DBConnectionUtil.getConnection();
					if(con!=null){
						System.out.println("Connect database successfully!");
					}
					// 2.query code
					PreparedStatement ps = con
							.prepareStatement("SELECT  * FROM Patient WHERE pid=?");
					ps.setInt(1, Integer.parseInt(pid.getText()));
					ResultSet rset = ps.executeQuery();
					if (!rset.next()) {
						JOptionPane.showMessageDialog(null,
								"Pid does not exsit!");
						return;
					}
					 ps = con
							.prepareStatement("SELECT  takes.tname, test.tdate, takes.result FROM Takes takes, Test test WHERE takes.tname=test.tname AND takes.pid = ?");
					ps.setString(1, pid.getText());
					 rset = ps.executeQuery();

			        DefaultTableModel tablemodel=new DefaultTableModel(); 
			        Object[] columns=new Object[3];   
			        tablemodel.addColumn("tname");
			        tablemodel.addColumn("tdate");
			        tablemodel.addColumn("result");
			        while (rset.next())                                
			        {
			            columns[0]=rset.getString(1);
			            columns[1]=rset.getString(2);
			            columns[2]=rset.getString(3);
			            tablemodel.addRow(columns);                    
			        }
			        rset.close();
					ps.close();
					jtable.setModel(tablemodel);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
		});
		
	}
}
